﻿Public Class Prestamo
    Dim conexion_bd As String

    Enum grabado
        ok
        errores
    End Enum

    Enum estado
        insertar
        modificar
    End Enum

    Enum termino
        aprobado
        rechazado
    End Enum

    Dim accion As estado = estado.modificar
    Dim grabar As grabado = grabado.errores


    Private Sub Prestamo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'conexión a la base de datos'
        Me.conexion_bd = "Provider=SQLOLEDB;Data Source=GTºN1\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=C:\DB\LIBRERIA_MAGOO.MDF"

        Me.habilitar(False)
        Me.btn_nuevo.Focus()
        Me.dtp_fechaInicio.MinDate = Date.Now
    End Sub


    Private Sub btn_buscarcli_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_buscarcli.Click
        If Me.txt_dni.Text = "" Or (Not (IsNumeric(Me.txt_dni.Text))) Then
            MessageBox.Show("Número de cliente incorrecto o incompleto", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            Me.buscar_clientes()
        End If
    End Sub

    Private Sub btn_guardar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_guardar.Click
        If Me.validar() = True Then
            If Me.accion = estado.insertar Then
                If Me.insertar_prestamo = grabado.errores Then
                    MessageBox.Show("Error al registrar el préstamo", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Else
                    If Me.insertar_detalle = grabado.errores Then
                        MessageBox.Show("Error al insertar detalle de préstamo ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Else
                        MessageBox.Show("Préstamo ingresado correctamente", "Préstamo Nro: " & Me.txt_numero.Text & "", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        habilitar(False)
                    End If
                End If
            End If
            End If

    End Sub

    Private Sub btn_nuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nuevo.Click
        Me.habilitar(True)
        Me.dtp_fechaInicio.Focus()
        Me.accion = estado.insertar
    End Sub

    Private Sub btn_quitar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_quitar.Click
        If (grilla.Rows.Count > 0 And (Not (IsNothing(grilla.CurrentRow)))) Then
            grilla.Rows.Remove(grilla.CurrentRow)
        Else
            MessageBox.Show("No ha seleccionado un libro para quitar de la grilla", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub btn_agregar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_agregar.Click
        If txt_titulo.Text <> "" And txt_isbn.Text <> "" Then

            Dim ban As Integer = 0

            For i As Integer = 0 To grilla.Rows.Count - 1
                If grilla.Rows(i).Cells(1).Value = txt_isbn.Text Then
                    ban = 1
                    MessageBox.Show("El libro seleccionado ya fue ingresado", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Exit For
                End If
            Next

            If Me.txt_idcliente.Text <> "" Then
                If verificarPrestamoExistente(Me.txt_isbn.Text) = True Then
                    ban = 1
                    MessageBox.Show("Ya existe un préstamo de este socio para el libro con ISBN: " & Me.txt_isbn.Text, "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
            Else
                MessageBox.Show("Debe ingresar un cliente antes de agregar un libro", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
                ban = 1
            End If



            If ban = 0 Then
                Me.grilla.Rows.Add(Me.txt_titulo.Text, Me.txt_isbn.Text)

                Me.txt_titulo.Text = ""
                Me.txt_isbn.Text = ""

                ban = 0

            End If
        Else
            MessageBox.Show("No hay un libro para agregar en la grilla", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        Me.txt_isbn.Text = ""
        Me.txt_titulo.Text = ""
    End Sub

    Private Sub btn_buscarart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_buscarart.Click
        If Me.txt_isbn.Text = "" Or (Not (IsNumeric(Me.txt_isbn.Text))) Then
            MessageBox.Show("Número ISBN incorrecto o vacío", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            Me.buscar_articulo()
        End If
    End Sub

    Private Sub habilitar(ByVal x As Boolean)
        Me.txt_cliente.Enabled = x
        Me.txt_idcliente.Enabled = x
        Me.txt_dni.Enabled = x
        Me.dtp_fechaInicio.Enabled = x
        Me.dtp_fechaFin.Enabled = x
        Me.txt_isbn.Enabled = x
        Me.txt_numero.Enabled = x
        Me.txt_titulo.Enabled = x
        Me.btn_nuevo.Enabled = Not x
        Me.btn_buscarart.Enabled = x
        Me.btn_buscarcli.Enabled = x
        Me.btn_agregar.Enabled = x
        Me.btn_guardar.Enabled = x
        Me.btn_quitar.Enabled = x

        Me.txt_cliente.Text = ""
        Me.txt_dni.Text = ""
        Me.txt_idcliente.Text = ""
        Me.dtp_fechaInicio.Text = ""
        Me.dtp_fechaFin.Text = ""
        Me.txt_isbn.Text = ""
        Me.txt_numero.Text = ""
        Me.txt_titulo.Text = ""

        Me.grilla.Rows.Clear()
    End Sub

    Private Sub buscar_clientes()
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim tabla As New Data.DataTable
        Dim consulta As String = ""

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consulta = "select apellido+', '+nombre as nombres, id_cliente from Cliente where num_doc= '" & Me.txt_dni.Text & "' "
        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion

        tabla.Load(command.ExecuteReader())

        If IsNumeric(Me.txt_dni.Text) = False Then
            MessageBox.Show("Número de documento inválido", "Mensaje de Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Me.txt_dni.Focus()
        Else
            If tabla.Rows.Count > 0 Then 'me fijo si se recuperó algo
                Me.txt_cliente.Text = tabla.Rows(0)("nombres")
                Me.txt_idcliente.Text = tabla.Rows(0)("id_cliente")
            Else
                MessageBox.Show("El Cliente no existe", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
                Me.txt_dni.Focus()
            End If
        End If
        conexion.Close()
    End Sub

    Private Sub buscar_articulo()
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim tabla As New Data.DataTable
        Dim consulta As String = ""

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consulta = "select titulo from Libro where isbn= '" & Me.txt_isbn.Text & "' "
        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion

        tabla.Load(command.ExecuteReader())

        If tabla.Rows.Count > 0 Then 'me fijo si se recuperó algo
            Me.txt_titulo.Text = tabla.Rows(0)("titulo")
        Else
            MessageBox.Show("El Artículo no existe", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
            Me.txt_isbn.Focus()
        End If

        conexion.Close()
    End Sub

    Private Function validar() As Boolean
        If Me.dtp_fechaInicio.Text = "" Then
            MessageBox.Show("La fecha está vacía", "Mensaje de Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Me.dtp_fechaInicio.Focus()
            Return False
        End If

        If Me.txt_dni.Text = "" Then
            MessageBox.Show("El documento está vacío", "Mensaje de Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Me.txt_dni.Focus()
            Return False
        End If

        If Me.txt_cliente.Text = "" Then
            MessageBox.Show("No se epecificó el Cliente", "Mensaje de Error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Me.txt_cliente.Focus()
            Return False
        End If

        If grilla.Rows.Count = 0 Then
            MessageBox.Show("Debe agregar al menos un libro para la reserva", "Mensaje de error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Return False
        End If

        If Me.dtp_fechaFin.Text <= Me.dtp_fechaInicio.Text Then
            MessageBox.Show("La fecha de fin de préstamo debe ser mayor a la de inicio", "Mensaje de error", MessageBoxButtons.OK, MessageBoxIcon.Stop, MessageBoxDefaultButton.Button1)
            Return False
        End If

        Return True

    End Function

    Private Function insertar_prestamo() As grabado
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim consulta As String = "" 'declaro la variable consulta, vacia'
        Dim tabla As New Data.DataTable


        'abro conexion'
        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()


        consulta = "insert into Prestamo (FechaInicio, IdCliente, FechaFin) values('" & Me.dtp_fechaInicio.Text & "'," & Me.txt_idcliente.Text & ",'" & Me.dtp_fechaFin.Text & "')"

        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion
        command.ExecuteNonQuery()

        conexion.Close()
        Me.tomar_num()
        Return grabado.ok
    End Function

    Private Function tomar_num()
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim tabla As New Data.DataTable
        Dim consulta As String = ""

        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consulta = "select * from Prestamo"

        command.CommandType = CommandType.Text
        command.CommandText = consulta
        command.Connection = conexion

        tabla.Load(command.ExecuteReader())
        Me.txt_numero.Text = tabla.Rows(tabla.Rows.Count - 1)("IdPrestamo")

        conexion.Close()
        Return tabla
    End Function

    Private Sub btn_salir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_salir.Click
        If MessageBox.Show("Cerrar esta ventana?", "Importante", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) Then
            Me.Close()
        End If
    End Sub

    Private Function verificarPrestamoExistente(ByVal isbn As String) As Boolean
        If Me.txt_idcliente.Text <> "" Then
            Dim conexion As New Data.OleDb.OleDbConnection
            Dim command As New Data.OleDb.OleDbCommand
            Dim tabla As New Data.DataTable
            Dim consulta As String = ""

            conexion.ConnectionString = Me.conexion_bd
            conexion.Open()

            consulta = "SELECT Prestamo.IdCliente as IdCliente, DetallePrestamo.ISBN as ISBN, Prestamo.FechaFin as FechaFin, DetallePrestamo.Estado as Estado FROM DetallePrestamo INNER JOIN Prestamo ON DetallePrestamo.IdPrestamo = Prestamo.IdPrestamo"

            command.CommandType = CommandType.Text
            command.CommandText = consulta
            command.Connection = conexion

            tabla.Load(command.ExecuteReader())

            For i As Integer = 0 To tabla.Rows.Count - 1
                If tabla.Rows(i)("ISBN") = isbn And tabla.Rows(i)("FechaFin") >= Date.Now.Date And tabla.Rows(i)("IdCliente") = Me.txt_idcliente.Text And tabla.Rows(i)("Estado") = "Abierto" Then
                    conexion.Close()
                    Return True
                End If
            Next
            conexion.Close()
        End If

        Return False
    End Function

    Private Function insertar_detalle() As grabado
        Dim conexion As New Data.OleDb.OleDbConnection
        Dim command As New Data.OleDb.OleDbCommand
        Dim consultaInsert As String = ""
        Dim consultaPrestamo As String = ""
        Dim tabla As New DataTable
        Dim IdPrestamo As Integer
        Dim i As Integer
        i = 0

        'abro conexion
        conexion.ConnectionString = Me.conexion_bd
        conexion.Open()

        consultaPrestamo = "select * from Prestamo"
        command.Connection = conexion
        command.CommandType = CommandType.Text
        command.CommandText = consultaPrestamo
        tabla.Load(command.ExecuteReader())

        IdPrestamo = tabla.Rows(tabla.Rows.Count - 1)("IdPrestamo")

        For i = 0 To Me.grilla.RowCount() - 1
            consultaInsert = "insert into DetallePrestamo (IdPrestamo, ISBN, Estado) values(" & IdPrestamo & "," & Me.grilla.Rows(i).Cells("ISBN").Value & ", " & "'Abierto'" & ")"
            command.CommandType = CommandType.Text
            command.CommandText = consultaInsert
            command.Connection = conexion
            command.ExecuteNonQuery()
            consultaInsert = ""
        Next

        conexion.Close()
        Return grabado.ok
    End Function


End Class